package com.ljkj.common.db.util;

import java.util.List;



public class SQLScriptGetter {

	/**
	 * ͨ��ʵ�����úõĶ������SQL�ű�
	 * @param setting
	 * @return String
	 */
	public static String getSQLScript(Setting setting) {
		StringBuilder builder = new StringBuilder();
		builder.append("from ");
		List<String> pojos = setting.getPojoNames();
		for (int i = 0; i < pojos.size(); i++) {
			if (i != 0) {
				builder.append(", ");
			}
			builder.append(pojos.get(i));
			builder.append(" as ");
			builder.append(getCaseFromClassName(pojos.get(i)));
		}
		boolean firstAppend = true;
		List<String> propertyNames = setting.getPropertyNames();
		List<Object> values = setting.getValues();
		if (propertyNames == null) return builder.toString();
		for (int i = 0; i < propertyNames.size(); i++) {
			if (propertyNames.get(i) == null) continue;
			if (propertyNames.get(i).equals("")) continue;
			if (values.get(i) == null) continue;
			if (values.get(i).equals("")) continue;
			if (values.get(i) instanceof Boolean) {
				if ((Boolean)values.get(i) == true) {
					values.set(i, 1);
				} else if ((Boolean)values.get(i) == false){
					values.set(i, 0);
				}
			}
			if (firstAppend) {
				builder.append(" where (");
				firstAppend = false;
			} else {
				builder.append(" and (");
			}
			if (propertyNames.get(i).indexOf(".") == -1 && pojos.size() == 1) {
				builder.append(getCaseFromClassName(pojos.get(0)));
				builder.append(".");
			}
			builder.append(propertyNames.get(i));
			if (values.get(i).toString().indexOf("!=") != -1) {
				values.set(i, values.get(i).toString().replaceAll("!=", ""));
				builder.append(" != ");
			} else if (values.get(i).toString().equals("null") || values.get(i).toString().equals("not null")){ 
				builder.append(" is ");
			}  else {
				builder.append(" = ");
			}
			boolean isField = false;
			for (int j = 0; j < pojos.size(); j++) {
				if (values.get(i).toString().indexOf(".") != -1) {
					if (values.get(i).toString().split("\\.")[0].equals(getCaseFromClassName(pojos.get(j)))){
						isField = true;
					}
				}
			}
			if (values.get(i).toString().equals("null") || values.get(i).toString().equals("not null")) {
				isField = true;
			}
			if (!isField) {
				builder.append("'");
			}
			builder.append(values.get(i));
			if (!isField) {
				builder.append("'");
			}
			builder.append(")");
		}
		if (setting.getLikePropertyNames() != null && setting.getLikeValues() != null) {
			builder = appendLikeSelect(pojos, builder, setting.getLikePropertyNames(), setting.getLikeValues()); 
		}
		if (setting.getBetweenPropertyNames() != null && setting.getBetweenValues() != null) {
			builder = appendBetweenSelect(pojos, builder, setting.getBetweenPropertyNames(), setting.getBetweenValues());
		}
		if (setting.getOrderByPropertyNames() != null && setting.getOrderByValues() != null) {
			builder = appendOrderBy(pojos, builder, setting.getOrderByPropertyNames(), setting.getOrderByValues());
		}
		return builder.toString();
	}
	/**
	 * 
	 * @param pojos
	 * @param propertyNames
	 * @param values
     * @deprecated As of version 1.0
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	private static String findByProperties(Class[] pojos, String[] propertyNames, Object[] values) {
		StringBuilder builder = new StringBuilder();
		builder.append("from ");
		for (int i = 0; i < pojos.length; i++) {
			if (i != 0) {
				builder.append(", ");
			}
			builder.append(pojos[i].getName());
			builder.append(" as ");
			builder.append(getCaseFromClassName(pojos[i].getName()));
		}
		boolean firstAppend = true;
		if (propertyNames == null) return builder.toString();
		for (int i = 0; i < propertyNames.length; i++) {
			if (propertyNames[i] == null) continue;
			if (propertyNames[i].equals("")) continue;
			if (values[i] == null) continue;
			if (values[i].equals("")) continue;
			if (values[i] instanceof Boolean) {
				if ((Boolean)values[i] == true) {
					values[i] = 1;
				} else if ((Boolean)values[i] == false){
					values[i] = 0;
				}
			}
			if (firstAppend) {
				builder.append(" where (");
				firstAppend = false;
			} else {
				builder.append(" and (");
			}
			if (propertyNames[i].indexOf(".") == -1 && pojos.length == 1) {
				builder.append(getCaseFromClassName(pojos[0].getName()));
				builder.append(".");
			}
			builder.append(propertyNames[i]);
			builder.append(" = ");

			boolean isField = false;
			for (int j = 0; j < pojos.length; j++) {
				if (values[i].toString().indexOf(".") != -1) {
					if (values[i].toString().split("\\.")[0].equals(getCaseFromClassName(pojos[j].getName()))){
						isField = true;
					}
				}
			}
			if (!isField) {
				builder.append("'");
			}
			builder.append(values[i]);
			if (!isField) {
				builder.append("'");
			}
			builder.append(")");
		}
		return builder.toString();
	}
	/**
	 * 
	 * @param pojos
	 * @param propertyNames
	 * @param values
     * @deprecated As of version 1.0
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public static String findByProperties(Class[] pojos, String[] propertyNames, Object[] values, String[] likePropertyNames, Object[] likevalues, String[] betweenPropertyNames, Object[][] betweenvalues, String[] orderByPropertyNames,Object[] orderByValues) {
		String queryString = findByProperties(pojos, propertyNames, values);
		queryString = appendLikeSelect(pojos, queryString, likePropertyNames, likevalues);
		queryString = appendBetweenSelect(pojos, queryString, betweenPropertyNames, betweenvalues);
		queryString = appendOrderBy(pojos, queryString, orderByPropertyNames, orderByValues);
		return queryString;
	}
	@SuppressWarnings("rawtypes")
	public static String findAndOrderByProperties(Class[] pojos, String[] propertyNames, Object[] values, String[] orderByPropertyNames, Object[] orderByValues) {
		String queryString = findByProperties(pojos, propertyNames, values);
		queryString = appendOrderBy(pojos, queryString, orderByPropertyNames, orderByValues);
		return queryString;
	}
	@SuppressWarnings("rawtypes")
	public static String getCountByProperties(Class[] pojos, String[] propertyNames, Object[] values) {
		StringBuilder builder = new StringBuilder();
		if (pojos.length > 1) {
			builder.append("select count(*) from ");
		} else if (pojos.length == 1) {
			builder.append("select count(");
			builder.append(getCaseFromClassName(pojos[0].getName()));
			builder.append(".id");
			builder.append(") from ");
		}
		for (int i = 0; i < pojos.length; i++) {
			if (i != 0) {
				builder.append(", ");
			}
			builder.append(pojos[i].getName());
			builder.append(" as ");
			builder.append(getCaseFromClassName(pojos[i].getName()));
		}
		boolean firstAppend = true;
		if (propertyNames == null) return builder.toString();
		for (int i = 0; i < propertyNames.length; i++) {
			if (propertyNames[i] == null) continue;
			if (propertyNames[i].equals("")) continue;
			if (values[i] == null) continue;
			if (values[i].equals("")) continue;
			if (values[i] instanceof Boolean) {
				if ((Boolean)values[i] == true) {
					values[i] = 1;
				} else if ((Boolean)values[i] == false){
					values[i] = 0;
				}
			}
			if (firstAppend) {
				builder.append(" where (");
				firstAppend = false;
			} else {
				builder.append(" and (");
			}
			if (propertyNames[i].indexOf(".") == -1 && pojos.length == 1) {
				builder.append(getCaseFromClassName(pojos[0].getName()));
				builder.append(".");
			}
			builder.append(propertyNames[i]);
			builder.append(" = ");

			boolean isField = false;
			for (int j = 0; j < pojos.length; j++) {
				if (values[i].toString().indexOf(".") != -1) {
					if (values[i].toString().split("\\.")[0].equals(getCaseFromClassName(pojos[j].getName()))){
						isField = true;
					}
				}
			}
			if (!isField) {
				builder.append("'");
			}
			builder.append(values[i]);
			if (!isField) {
				builder.append("'");
			}
			builder.append(")");
		}
		return builder.toString();
	}

	@SuppressWarnings("rawtypes")
	public static String getCountByProperties(Class[] pojos, String[] propertyNames, Object[] values, String[] likePropertyNames, Object[] likevalues, String[] betweenPropertyNames, Object[][] betweenvalues) {
		String queryString = getCountByProperties(pojos, propertyNames, values);
		queryString = appendLikeSelect(pojos, queryString, likePropertyNames, likevalues);
		queryString = appendBetweenSelect(pojos, queryString, betweenPropertyNames, betweenvalues);
		return queryString;
	}
	
	/**
	 * 
	 * @param pojos
	 * @param propertyNames
	 * @param values
     * @deprecated As of version 1.0
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	private static String appendLikeSelect(Class[] pojos, String queryString, String[] likePropertyNames, Object[] likevalues){
		if (likePropertyNames == null || likevalues == null) return queryString;
		StringBuilder builder = new StringBuilder();
		builder.append(queryString);
		for (int i = 0; i < likePropertyNames.length; i++) {
			if (likePropertyNames[i].equals("")) continue;
			if (likevalues[i] == null || likevalues[i].equals("")) continue;
			if (likevalues[i].toString().indexOf(" ") != -1) {
				String[] values = likevalues[i].toString().split(" ");
				for (int j = 0; j < values.length; j ++){
					if (values[j].equals("")) {
						continue;
					}
					if (builder.toString().indexOf("like") == -1){
						if (builder.toString().indexOf("where") != -1) {
							builder.append(" and (");
						} else {
							builder.append(" where (");
						}
						if (likePropertyNames[i].indexOf(".") == -1 && pojos.length == 1) {
							builder.append(getCaseFromClassName(pojos[0].getName()));
							builder.append(".");
						}
						builder.append(likePropertyNames[i]);
						builder.append(" like '%");
						builder.append(values[j]);
						builder.append("%'");
					} else {
						if (builder.toString().indexOf("where") != -1) {
							builder.append(" or ");
						} else {
							builder.append(" where ");
						}
						if (likePropertyNames[i].indexOf(".") == -1 && pojos.length == 1) {
							builder.append(getCaseFromClassName(pojos[0].getName()));
							builder.append(".");
						}
						builder.append(likePropertyNames[i]);
						builder.append(" like '%");
						builder.append(values[j]);
						builder.append("%'");
					}
				}
			} else {
				if (builder.toString().indexOf("like") == -1){
					if (builder.toString().indexOf("where") != -1) {
						builder.append(" and (");
					} else {
						builder.append(" where (");
					}
					if (likePropertyNames[i].indexOf(".") == -1 && pojos.length == 1) {
						builder.append(getCaseFromClassName(pojos[0].getName()));
						builder.append(".");
					}
					builder.append(likePropertyNames[i]);
					builder.append(" like '%");
					builder.append(likevalues[i]);
					builder.append("%'");
				} else {
					if (builder.toString().indexOf("where") != -1) {
						builder.append(" or ");
					} else {
						builder.append(" where ");
					}
					if (likePropertyNames[i].indexOf(".") == -1 && pojos.length == 1) {
						builder.append(getCaseFromClassName(pojos[0].getName()));
						builder.append(".");
					}
					builder.append(likePropertyNames[i]);
					builder.append(" like '%");
					builder.append(likevalues[i]);
					builder.append("%'");
				}
			}
		}
		if (builder.toString().indexOf("like") != -1 && builder.toString().charAt(builder.toString().length()-1) != ')'){
			builder.append(")");
		}
		return builder.toString();
	}

	private static StringBuilder appendLikeSelect(List<String> pojos, StringBuilder builder, List<String> likePropertyNames, List<Object> likevalues){
		for (int i = 0; i < likePropertyNames.size(); i++) {
			if (likePropertyNames.get(i).equals("")) continue;
			if (likevalues.get(i) == null || likevalues.get(i).equals("")) continue;
			if (likevalues.get(i).toString().indexOf(" ") != -1) {
				String[] values = likevalues.get(i).toString().split(" ");
				for (int j = 0; j < values.length; j ++){
					if (values[j].equals("")) {
						continue;
					}
					if (builder.toString().indexOf("like") == -1){
						if (builder.toString().indexOf("where") != -1) {
							builder.append(" and (");
						} else {
							builder.append(" where (");
						}
						if (likePropertyNames.get(i).indexOf(".") == -1 && pojos.size() == 1) {
							builder.append(getCaseFromClassName(pojos.get(0)));
							builder.append(".");
						}
						builder.append(likePropertyNames.get(i));
						builder.append(" like '%");
						builder.append(values[j]);
						builder.append("%'");
					} else {
						if (builder.toString().indexOf("where") != -1) {
							builder.append(" or ");
						} else {
							builder.append(" where ");
						}
						if (likePropertyNames.get(i).indexOf(".") == -1 && pojos.size() == 1) {
							builder.append(getCaseFromClassName(pojos.get(0)));
							builder.append(".");
						}
						builder.append(likePropertyNames.get(i));
						builder.append(" like '%");
						builder.append(values[j]);
						builder.append("%'");
					}
				}
			} else {
				if (builder.toString().indexOf("like") == -1){
					if (builder.toString().indexOf("where") != -1) {
						builder.append(" and (");
					} else {
						builder.append(" where (");
					}
					if (likePropertyNames.get(i).indexOf(".") == -1 && pojos.size() == 1) {
						builder.append(getCaseFromClassName(pojos.get(0)));
						builder.append(".");
					}
					builder.append(likePropertyNames.get(i));
					builder.append(" like '%");
					builder.append(likevalues.get(i));
					builder.append("%'");
				} else {
					if (builder.toString().indexOf("where") != -1) {
						builder.append(" or ");
					} else {
						builder.append(" where ");
					}
					if (likePropertyNames.get(i).indexOf(".") == -1 && pojos.size() == 1) {
						builder.append(getCaseFromClassName(pojos.get(0)));
						builder.append(".");
					}
					builder.append(likePropertyNames.get(i));
					builder.append(" like '%");
					builder.append(likevalues.get(i));
					builder.append("%'");
				}
			}
		}
		if (builder.toString().indexOf("like") != -1 && builder.toString().charAt(builder.toString().length()-1) != ')'){
			builder.append(")");
		}
		return builder;
	}
	
	/**
	 * 
	 * @param pojos
	 * @param propertyNames
	 * @param values
     * @deprecated As of version 1.0
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	private static String appendBetweenSelect(Class[] pojos, String queryString, String[] betweenPropertyNames, Object[][] betweenvalues){
		if (betweenPropertyNames == null || betweenvalues == null) return queryString;
		StringBuilder builder = new StringBuilder();
		builder.append(queryString);
		for (int i = 0; i < betweenPropertyNames.length; i++) {
			if (betweenPropertyNames[i].equals("")) continue;
			if (betweenvalues[i] == null) continue;
			if (betweenvalues[i][0] == null || betweenvalues[i][0].equals("") || betweenvalues[i][0].equals("null")) continue;
			if (betweenvalues[i][1] == null || betweenvalues[i][1].equals("") || betweenvalues[i][1].equals("null")) continue;
			if (i <= betweenPropertyNames.length-1) {
				if (builder.toString().indexOf("where") != -1) {
					builder.append(" and (");
				} else {
					builder.append(" where (");
				}
				if (betweenPropertyNames[i].indexOf(".") == -1 && pojos.length == 1) {
					builder.append(getCaseFromClassName(pojos[0].getName()));
					builder.append(".");
				}
				builder.append(betweenPropertyNames[i]);
				builder.append(" between '");
				builder.append(betweenvalues[i][0]);
				builder.append("' and '");
				builder.append( betweenvalues[i][1]);
				builder.append("')");
			}
		}
		return builder.toString();
	}
	private static StringBuilder appendBetweenSelect(List<String> pojos, StringBuilder builder, List<String> betweenPropertyNames, List<List<Object>> betweenvalues){
		for (int i = 0; i < betweenPropertyNames.size(); i++) {
			if (betweenPropertyNames.get(i).equals("")) continue;
			if (betweenvalues.get(i) == null) continue;
			if (betweenvalues.get(i).get(i) == null || betweenvalues.get(i).get(i).equals("") || betweenvalues.get(i).get(i).equals("null")) continue;
			if (betweenvalues.get(i).get(1) == null || betweenvalues.get(i).get(1).equals("") || betweenvalues.get(i).get(1).equals("null")) continue;
			if (i <= betweenPropertyNames.size() - 1) {
				if (builder.toString().indexOf("where") != -1) {
					builder.append(" and (");
				} else {
					builder.append(" where (");
				}
				if (betweenPropertyNames.get(i).indexOf(".") == -1 && pojos.size() == 1) {
					builder.append(getCaseFromClassName(pojos.get(i)));
					builder.append(".");
				}
				builder.append(betweenPropertyNames.get(i));
				builder.append(" between '");
				builder.append(betweenvalues.get(i).get(i));
				builder.append("' and '");
				builder.append( betweenvalues.get(i).get(1));
				builder.append("')");
			}
		}
		return builder;
	}
	/**
	 * 
	 * @param pojos
	 * @param propertyNames
	 * @param values
     * @deprecated As of version 1.0
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	private static String appendOrderBy(Class[] pojos, String queryString, String[] orderByPropertyNames, Object[] orderByValues){
		if (orderByPropertyNames == null || orderByValues == null) return queryString;
		StringBuilder builder = new StringBuilder();
		builder.append(queryString);
		boolean b = false;
		for (int i = 0; i < orderByPropertyNames.length; i++) {
			if (orderByPropertyNames[i].equals("")) continue;
			if (orderByValues[i] == null) continue;
			if (b) {
				builder.append(", ");
				if (orderByPropertyNames[i].indexOf(".") == -1 && pojos.length == 1) {
					builder.append(getCaseFromClassName(pojos[0].getName()));
					builder.append(".");
				}
				builder.append(orderByPropertyNames[i]);
				builder.append(" ");
				builder.append(orderByValues[i]);
			} else {
				
				builder.append(" order by ");
				if("userInfo.userName".equals(orderByPropertyNames[0])){
					if(orderByValues[0]!=null){
						b = true;
						builder.append("(CASE WHEN INSTR(user_name,'"+orderByValues[0]+"')> 0 THEN 1 ELSE 0 END ) desc");
						
					}
					continue;
				}	
				if (orderByPropertyNames[i].indexOf(".") == -1 && pojos.length == 1) {
					builder.append(getCaseFromClassName(pojos[0].getName()));
					builder.append(".");
				}
				builder.append(orderByPropertyNames[i]);
				builder.append(" ");
				builder.append(orderByValues[i]);
				b = true;
			}
		}
		return builder.toString();
	}
	private static StringBuilder appendOrderBy(List<String> pojos, StringBuilder builder, List<String> orderByPropertyNames, List<Object> orderByValues){
		boolean b = false;
		for (int i = 0; i < orderByPropertyNames.size(); i++) {
			if (orderByPropertyNames.get(i).equals("")) continue;
			if (orderByValues.get(i) == null) continue;
			if (b) {
				builder.append(", ");
				if (orderByPropertyNames.get(i).indexOf(".") == -1 && pojos.size() == 1) {
					builder.append(getCaseFromClassName(pojos.get(0)));
					builder.append(".");
				}
				builder.append(orderByPropertyNames.get(i));
				builder.append(" ");
				builder.append(orderByValues.get(i));
			} else {
				builder.append(" order by ");
				if (orderByPropertyNames.get(i).indexOf(".") == -1 && pojos.size() == 1) {
					builder.append(getCaseFromClassName(pojos.get(0)));
					builder.append(".");
				}
				builder.append(orderByPropertyNames.get(i));
				builder.append(" ");
				builder.append(orderByValues.get(i));
				b = true;
			}
		}
		return builder;
	}
	
	private static String getCaseFromClassName(String className){
		StringBuilder builder = new StringBuilder();
		if (className.indexOf(".") != -1) {
			className = className.split("\\.")[className.split("\\.").length - 1];
		}
		builder.append(String.valueOf(className.charAt(0)).toLowerCase());
		builder.append(className.substring(1, className.length())); 
		return builder.toString();
	}
	
}
